from django.db import models
from tools import mysqlHelper
from tools import mssqlHelper

# Create your models here.

class user_test(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=40)
    email = models.EmailField()

    def __unicode__(self):
        return u'%s %s' % (self.first_name, self.last_name)

    def getuser_data(self):
        sql ='''
        select * from MYTEST_USER_TEST
        '''
        fetchall= mysqlHelper.getdata(sql)
        rs=[]
        for obj in fetchall:
            dic={}
            dic['id']=obj[0]
            dic['first_name']=obj[1]
            dic['last_name']=obj[2]
            dic['email']=obj[3]
            rs.append(dic)
        return rs
    def getuser_data_para(self,para):
        sql ='''
        select * from MYTEST_USER_TEST WHERE id = %s and first_name = %s
        '''
        fetchall= mysqlHelper.getdata_param(sql,para)
        return fetchall

    def getuser_data_dic(self):
        sql ='''
        select * from MYTEST_USER_TEST
        '''
        fetchall= mysqlHelper.getdata(sql)
        return fetchall

class mssql_test(models.Model):
    def get_dim_date(self):
        sql ='''
        SELECT TOP 10 [date1],[date3],[year],[month],[day] FROM [dw].[dbo].[dim_date]
        '''
        fetchall= mssqlHelper.getdata(sql)
        return fetchall

    def get_orderamount(self):
        sql ='''
        SELECT a.product_type,cast(SUM(a.orderamount) as varchar(20)) orderamount
        FROM dw.[dbo].[olap_order_stat] a WITH(NOLOCK)
        JOIN bi.dbo.[dim_product_id]  b ON a.product_type = b.id
        WHERE inputdate = CONVERT(VARCHAR(10),GETDATE()-1,120)
        GROUP BY a.product_type
        ORDER  BY 1
        '''
        fetchall= mssqlHelper.getdata(sql)
        return fetchall

class tmptest(models.Model):
    def get_tmp_order_stat(self):
        sql ='''
        SELECT a.product_type,orderamount FROM tmp_order_stat a ORDER  BY 1
        '''
        fetchall= mysqlHelper.getdata(sql,False)
        return fetchall